package com.module.codeBind.dao;
import java.lang.reflect.InvocationTargetException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.beanutils.PropertyUtils;
import org.hibernate.Query;
import org.hibernate.Session;

import base.common.CUDEnum;
import base.dao.GenericHibernateDao;
import base.util.Page;
import base.util.StringUtil;
import base.util.Tools;
import base.util.datetime.DateTimeUtil;

import com.module.codeBind.vo.BindBatchCode;
import com.module.codeBind.vo.CodeBind;
import com.module.codeBind.vo.CodeBindSyn;

public class CodeBindDao extends  GenericHibernateDao<CodeBind,String>{
	private AppAttrValueDao appAttrValuedao = AppAttrValueDao.getInstance();
	private BindBatchCodeDao bindBatchCodeDao = BindBatchCodeDao.getInstance();
	private BindCodeAppDao bindCodeAppDao = BindCodeAppDao.getInstance();
	private CodeBindSynDao bindSynDao = CodeBindSynDao.getInstance();
	private BindBatchCodeSynDao batchCodeSynDao = BindBatchCodeSynDao.getInstance();
	
    private static CodeBindDao dao = new CodeBindDao();
    private CodeBindDao(){ }
    public static CodeBindDao getInstance(){
        return dao;
    }

    /**
     * 保存码绑定
     * @param bean 绑定实体对象
     * @author mayb
     * @throws NoSuchMethodException 
     * @throws InvocationTargetException 
     * @throws IllegalAccessException 
     */
    public void saveCodeBind(Session session,CodeBind bean) {
        String bindId = bean.getId();
        boolean flag = false;
        try{
        	if(Tools.isValid(bindId)){
    			CodeBind beanEld = this.findById(session, bindId);
    			//添加endw_bing_syn记录
    			flag = bindSynDao.updateCodeBindSyn(session,beanEld,bean);
    			
    			session.update(bean);
    		}else {
    			
    			bindId = (String)session.save(bean);
    			//添加endw_bing_syn记录
    			CodeBindSyn bindSyn = new CodeBindSyn();
    			PropertyUtils.copyProperties(bindSyn,bean);//对比数据，并构建目标对象
//    			bindSyn.setId(bindId);
    			bindSyn.setSyscStatus(CUDEnum.C.toString());
    			flag = bindSynDao.saveCodeBindSyn(session, bindSyn);
    		}
    		if(bean.getAttrValueList() != null && bean.getAttrValueList().size()>0){
    			appAttrValuedao.save(session,bean.getAttrValueList(),bindId,bean.getCreaterId());
    		}
    		bindBatchCodeDao.save(session,bean.getBatchCodeList(), bindId);
    		//添加endw_bind_batch_code_syn记录
    		List<BindBatchCode> batchCodes = bindBatchCodeDao.findBySQL(session, "from BindBatchCode as obj where obj.bindId='"+bindId+"'");
    		batchCodeSynDao.saveSyn(session, batchCodes);
        }catch(Exception e){
        	
        }
		
    }
    /**
     * 保存前校验绑定批次号是否重复
     * @param session
     * @param bean
     * @return
     */
    public boolean verifyBatchNoIsRepeat(Session session,CodeBind bean){
    	boolean canSave =false;
    	List<String> batchCodeList = bean.getBatchCodeList();
    	if(batchCodeList == null || batchCodeList.size()==0){
    		return canSave;
    	}
    	String batchCodeStr = StringUtil.getNewStrByList(batchCodeList);
    	StringBuffer sql=new StringBuffer("select count(distinct eb.id)");
    	sql.append(" from CodeBind as eb,BindBatchCode as ebbc");
    	sql.append(" where eb.status !='D' and eb.id = ebbc.bindId");
    	sql.append(" and ebbc.batchCodeId in (").append(batchCodeStr).append(")");
    	if(Tools.isValid(bean.getId())){
    		sql.append(" and eb.id !='").append(bean.getId()).append("'");
    	}
    	
    	Query totalQuery = session.createQuery(sql.toString());
    	Long count = (Long) totalQuery.uniqueResult();
    	if(count==0){
    		canSave =true;
    	}
    	
    	return canSave;
    }
    /**
     * 保存前校验商品是否重复绑定一样的应用
     * @param session
     * @param bean
     * @return
     */
    public boolean verifyProductIsRepeatBindSameApp(Session session,CodeBind bean){
    	boolean canSave =false;
    	StringBuffer sql=new StringBuffer("select count(distinct eb.id)");
    	sql.append(" from CodeBind as eb,BindBatchCode as ebbc,BatchCode as ebc");
    	sql.append(" where eb.status !='D' and eb.id = ebbc.bindId and ebbc.batchCodeId=ebc.id ");
    	sql.append(" and eb.codeAppId='").append(bean.getCodeAppId()).append("'");
    	sql.append(" and ebc.productId ='").append(bean.getProductId()).append("'");
    	if(Tools.isValid(bean.getId())){
    		sql.append(" and eb.id !='").append(bean.getId()).append("'");
    	}
    	Query totalQuery = session.createQuery(sql.toString());
    	Long count = (Long) totalQuery.uniqueResult();
    	if(count==0){
    		canSave =true;
    	}
    	return canSave;
    }
    public CodeBind findById(Session session,String id){
    	CodeBind bean = this.get(session, id);
    	bean.setProductId(getProductIdByBindId(session,id));
    	bean.setBatchCodeList(bindBatchCodeDao.getListByBindId(session,id));
    	bean.setCodeAppList(bindCodeAppDao.getListByBindId(session,id));
    	return bean;
    }
    
    
    /**
	 * 分页查询所有
	 * @param bean 码绑定对象
	 * @param start 开始行
	 * @param limit 每一页的最大行数
	 * @return Page 分页对象
     * @throws SQLException 
	 */
	@SuppressWarnings("unchecked")
	public Page toList(Session session,CodeBind bean,Integer start,Integer limit) throws SQLException{
		Page page = new Page();		 
		page.setStart(start);
		page.setLimit(limit);
		
    	StringBuffer sql= new StringBuffer("select distinct eb.id, eb.bindTime,ebc.productName,ca.name");
    	sql.append(" from CodeBind as eb,BindBatchCode as ebbc");
    	sql.append(",BatchCode as ebc,CodeApp as ca");
    	sql.append(" where eb.status !='D' and eb.id = ebbc.bindId and ebbc.batchCodeId=ebc.id ");
    	sql.append(" and eb.codeAppId=ca.id");
    	sql.append(" and eb.orgId in ("+bean.getOrgId()+")");
    	StringBuffer contSql=new StringBuffer("select count(distinct eb.id) ");
    	contSql.append("from CodeBind as eb,BindBatchCode as ebbc");
    	contSql.append(",BatchCode as ebc,CodeApp as ca");
    	contSql.append(" where eb.status !='D' and eb.id = ebbc.bindId and ebbc.batchCodeId=ebc.id ");
    	contSql.append(" and eb.codeAppId=ca.id");
    	contSql.append(" and eb.orgId in ("+bean.getOrgId()+")");
		StringBuffer condition = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		if(Tools.isValid(bean)){
			if(Tools.isValid(bean.getCodeAppId())){
				condition.append(" and codeAppId like '%"+bean.getCodeAppId()+"%'");
			}
			if(Tools.isValid(bean.getProductId())){
				condition.append(" and ebc.productId = ?");
				params.add(bean.getProductId());
			}
			if(Tools.isValid(bean.getBatchNo())){
				condition.append(" and ebc.batchNo like '%"+bean.getBatchNo()+"%'");
			}
			if (Tools.isValid(bean.getBindTime())) {
				condition.append(" and eb.bindTime >= ?");
				params.add(DateTimeUtil.adjustStartDate(bean.getBindTime()));
			}
			if (Tools.isValid(bean.getBindTimeEnd())) {
				condition.append(" and eb.bindTime <=?");
				params.add(DateTimeUtil.adjustEndDate(bean.getBindTimeEnd()));
			}
		}
		sql.append(condition);
		contSql.append(condition);
		sql.append(" ORDER BY eb.bindTime desc");
		Query query = session.createQuery(sql.toString());
		int i = 0;
		for (Object obj : params) {
			query.setParameter(i, obj);
			i++;
		}
		query.setFirstResult(start).setMaxResults(limit);
		Query totalQuery = session.createQuery(contSql.toString());
		i = 0;
		for (Object obj : params) {
			totalQuery.setParameter(i, obj);
			i++;
		}
		page.setTotal((long) ((Long) totalQuery.uniqueResult()).intValue());
		List result = query.list();
		Iterator it = result.iterator();  
		List list = new ArrayList();
    	while (it.hasNext()) {     
    	   CodeBind po = new CodeBind();
    	   Object[] tuple = (Object[]) it.next();   
    	   po.setId((String)tuple[0]);
    	   po.setBindTime((Date)tuple[1]);
    	   po.setProductName((String)tuple[2]);
    	   po.setCodeAppName((String)tuple[3]);
    	   
    	   List batchNoList = session.createQuery("select ebc.batchNo from BindBatchCode as ebbc,BatchCode as ebc where ebbc.batchCodeId=ebc.id and ebbc.bindId='"+po.getId()+"'").list();
    	   po.setBatchCodeStr(StringUtil.getNewStrByList2(batchNoList));
    	   list.add(po);
    	}  
		page.setRows(list);
		return page;
    }
	
	/**
	 * 逻辑删除
	 * @param obj bean对象
	 * @return 返回成功或者失败
	 */
    public void deleteCodeBind(Session session,CodeBind obj){
            String hql = "update CodeBind set updaterId = ?,updateTime=?,status =? where id in ("+obj.getId()+")";
            Query query  = session.createQuery(hql); 
            query.setParameter(0,obj.getUpdaterId());
            query.setParameter(1, new Date());
            query.setParameter(2, CUDEnum.D.toString());
            query.executeUpdate();
            appAttrValuedao.delete(session, obj);
            //将删除操作记录到码绑定增量表
			try {
				
				String[] idArray = obj.getId().split(",");
				for(int i=0;i<idArray.length;i++){
					CodeBindSyn bindSyn = new CodeBindSyn();
					PropertyUtils.copyProperties(bindSyn,obj);//对比数据，并构建目标对象
					String id = idArray[i].replace("'", "");
					bindSyn.setId(id);
					bindSyn.setOrgId(obj.getOrgId());
					bindSyn.setSyscStatus(CUDEnum.D.toString());
					bindSynDao.saveCodeBindSyn(session, bindSyn);
				}
			} catch (IllegalAccessException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (InvocationTargetException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (NoSuchMethodException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

            
    }
    @SuppressWarnings("unchecked")
	public String getProductByCode(Session session,String code){
    	String sql = "select a.c_product_id from endw_bind a left join endw_code b " +
    			"on a.c_bind_id = b.c_bind_id where b.c_code='"+code+"'";
    	List<String> list = session.createSQLQuery(sql).list();
    	if(list != null && !list.isEmpty())
    		return list.get(0);
    	return null;
    }
    @SuppressWarnings("unchecked")
	public String getProductIdByBindId(Session session,String bindId){
    	String sql = "select c.c_product_id from endw_bind a join endw_bind_batch_code b on a.c_bind_id = b.c_bind_id join endw_batch_code c " +
    			"on b.c_batch_code_id = c.c_id where a.c_bind_id='"+bindId+"'";
    	List<String> list = session.createSQLQuery(sql).list();
    	if(list != null && !list.isEmpty())
    		return list.get(0);
    	return null;
    }
    
}
